package reporting;

import java.io.FileWriter;
import java.io.File;
import database.DBQueries;
import java.sql.ResultSet;
import au.com.bytecode.opencsv.CSVWriter;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import javax.swing.JFileChooser;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import java.io.IOException;
import java.awt.Desktop;

/**
 * Containing all things GUI for JHAVE Report Generator
 * @author Gabrielle DuBois
 * @author Rick LaChapelle
 */
public class gui extends javax.swing.JFrame {

    private ResultSet rsToSave;
    private boolean includeHeadersInCSVFile = true;

    /** Creates new form gui */
    public gui() {
        initComponents();
        DefaultTableModel defaultTableModel = new DefaultTableModel(100, 4);
        jtable.setModel(defaultTableModel);
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        btngDates = new javax.swing.ButtonGroup();
        jScrollPane1 = new javax.swing.JScrollPane();
        jtable = new javax.swing.JTable();
        jPanel1 = new javax.swing.JPanel();
        btnQuery = new javax.swing.JButton();
        cboQueries = new javax.swing.JComboBox();
        jPanel2 = new javax.swing.JPanel();
        lblEndDate = new javax.swing.JLabel();
        txtEndMonth = new javax.swing.JTextField();
        lblDash3 = new javax.swing.JLabel();
        txtEndDay = new javax.swing.JTextField();
        lblDash4 = new javax.swing.JLabel();
        txtEndYear = new javax.swing.JTextField();
        jPanel3 = new javax.swing.JPanel();
        lblStartDate = new javax.swing.JLabel();
        txtStartMonth = new javax.swing.JTextField();
        lblDash1 = new javax.swing.JLabel();
        txtStartDay = new javax.swing.JTextField();
        lblDash2 = new javax.swing.JLabel();
        txtStartYear = new javax.swing.JTextField();
        rbtnAllDates = new javax.swing.JRadioButton();
        rbtnStartEndDates = new javax.swing.JRadioButton();
        jMenuBar1 = new javax.swing.JMenuBar();
        jMenu1 = new javax.swing.JMenu();
        menuSaveAs = new javax.swing.JMenuItem();
        jSeparator1 = new javax.swing.JPopupMenu.Separator();
        menuExit = new javax.swing.JMenuItem();
        menuManual = new javax.swing.JMenu();
        jMenuItem1 = new javax.swing.JMenuItem();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("JHAVE Report Generator");
        setResizable(false);

        jtable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "", "", "", ""
            }
        ));
        jtable.setAutoResizeMode(javax.swing.JTable.AUTO_RESIZE_ALL_COLUMNS);
        jtable.setDropMode(javax.swing.DropMode.INSERT_ROWS);
        jtable.setOpaque(false);
        jtable.setRowSelectionAllowed(false);
        jtable.setSelectionBackground(new java.awt.Color(0, 153, 153));
        jScrollPane1.setViewportView(jtable);

        btnQuery.setText("Query");
        btnQuery.setToolTipText("Click to run a query and produce results in table below");
        btnQuery.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnQueryActionPerformed(evt);
            }
        });

        cboQueries.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "'Q1: Group by country, then city, then visualization name'", "'Q2: Group by visualization name, then country, then city'", "'Q3: Group by country, then city, then user name'" }));
        cboQueries.setToolTipText("Select a Query");
        cboQueries.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                cboQueriesActionPerformed(evt);
            }
        });

        jPanel2.setEnabled(false);

        lblEndDate.setText("End Date:");
        lblEndDate.setEnabled(false);

        txtEndMonth.setText("04");
        txtEndMonth.setEnabled(false);
        txtEndMonth.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtEndMonthActionPerformed(evt);
            }
        });

        lblDash3.setText("/");
        lblDash3.setEnabled(false);

        txtEndDay.setText("18");
        txtEndDay.setEnabled(false);
        txtEndDay.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtEndDayActionPerformed(evt);
            }
        });

        lblDash4.setText("/");
        lblDash4.setEnabled(false);

        txtEndYear.setText("2011");
        txtEndYear.setEnabled(false);
        txtEndYear.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtEndYearActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                .addComponent(lblEndDate)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 33, Short.MAX_VALUE)
                .addComponent(txtEndMonth, javax.swing.GroupLayout.PREFERRED_SIZE, 39, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblDash3)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(txtEndDay, javax.swing.GroupLayout.PREFERRED_SIZE, 39, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblDash4)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(txtEndYear, javax.swing.GroupLayout.PREFERRED_SIZE, 63, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(txtEndMonth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lblDash3)
                    .addComponent(txtEndDay, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lblDash4)
                    .addComponent(txtEndYear, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lblEndDate))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        jPanel3.setEnabled(false);

        lblStartDate.setText("Start Date:");
        lblStartDate.setEnabled(false);

        txtStartMonth.setText("04");
        txtStartMonth.setEnabled(false);
        txtStartMonth.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtStartMonthActionPerformed(evt);
            }
        });

        lblDash1.setText("/");
        lblDash1.setEnabled(false);

        txtStartDay.setText("17");
        txtStartDay.setEnabled(false);
        txtStartDay.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtStartDayActionPerformed(evt);
            }
        });

        lblDash2.setText("/");
        lblDash2.setEnabled(false);

        txtStartYear.setColumns(4);
        txtStartYear.setText("2011");
        txtStartYear.setEnabled(false);
        txtStartYear.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtStartYearActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel3Layout.createSequentialGroup()
                .addComponent(lblStartDate)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 44, Short.MAX_VALUE)
                .addComponent(txtStartMonth, javax.swing.GroupLayout.PREFERRED_SIZE, 39, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblDash1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(txtStartDay, javax.swing.GroupLayout.PREFERRED_SIZE, 39, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(lblDash2)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(txtStartYear, javax.swing.GroupLayout.PREFERRED_SIZE, 63, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(txtStartMonth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lblDash1)
                    .addComponent(txtStartDay, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lblDash2)
                    .addComponent(txtStartYear, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lblStartDate))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        btngDates.add(rbtnAllDates);
        rbtnAllDates.setSelected(true);
        rbtnAllDates.setText(" All Dates");
        rbtnAllDates.setToolTipText("Select to query all dates from the begining to today's date");
        rbtnAllDates.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                rbtnAllDatesActionPerformed(evt);
            }
        });

        btngDates.add(rbtnStartEndDates);
        rbtnStartEndDates.setToolTipText("Select to query a specific date range");
        rbtnStartEndDates.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                rbtnStartEndDatesActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(rbtnStartEndDates)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(cboQueries, javax.swing.GroupLayout.PREFERRED_SIZE, 644, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(rbtnAllDates)
                    .addComponent(btnQuery))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addComponent(cboQueries, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(6, 6, 6)
                .addComponent(rbtnAllDates)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jPanel3, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(rbtnStartEndDates)
                    .addComponent(jPanel2, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(btnQuery))
        );

        jMenu1.setText("File");

        menuSaveAs.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_S, java.awt.event.InputEvent.CTRL_MASK));
        menuSaveAs.setText("Save As...");
        menuSaveAs.setEnabled(false);
        menuSaveAs.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                menuSaveAsActionPerformed(evt);
            }
        });
        jMenu1.add(menuSaveAs);
        jMenu1.add(jSeparator1);

        menuExit.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_Q, java.awt.event.InputEvent.CTRL_MASK));
        menuExit.setText("Exit");
        menuExit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                menuExitActionPerformed(evt);
            }
        });
        jMenu1.add(menuExit);

        jMenuBar1.add(jMenu1);

        menuManual.setText("Help");

        jMenuItem1.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_U, java.awt.event.InputEvent.CTRL_MASK));
        jMenuItem1.setText("User Manual...");
        jMenuItem1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jMenuItem1ActionPerformed(evt);
            }
        });
        menuManual.add(jMenuItem1);

        jMenuBar1.add(menuManual);

        setJMenuBar(jMenuBar1);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(32, 32, 32)
                        .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 736, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 532, Short.MAX_VALUE)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void verifyDateInput() {
        /* TODO: Verify user input some how...
        //starting date
        int sMonth = Integer.parseInt(txtStartMonth.getText());
        int sDay = Integer.parseInt(txtStartDay.getText());
        int sYear = Integer.parseInt(txtStartYear.getText());

        //ending date
        int eMonth = Integer.parseInt(txtEndMonth.getText());
        int eDay = Integer.parseInt(txtEndDay.getText());
        int eYear = Integer.parseInt(txtEndYear.getText());
         */
    }

    private Date setStringToDate(String date) {
        Date date1 = null;
        try {
            DateFormat formatter = new SimpleDateFormat("MM/dd/yy");
            date1 = formatter.parse(date);
            //System.out.println("Date: " + date1);

        } catch (ParseException ex) {
            System.out.println("Date Parse Error: " + ex);
        } catch (NullPointerException ex) {
            System.out.println("Date is null");
        }
        return date1;
    }

    public String getStartDate() {
        String startdate = null;
        try {
            startdate = txtStartMonth.getText()
                    + "/" + txtStartDay.getText()
                    + "/" + txtStartYear.getText().substring(2, 4);
            //System.out.println("Start Date: " + startdate);
        } catch (StringIndexOutOfBoundsException ex) {
            JOptionPane.showMessageDialog(this, "Start date Entered Incorrectly\n    Please Try Again", "Date Error", JOptionPane.ERROR_MESSAGE);
        }
        return startdate;
    }

    public String getEndDate() {
        String endDate = null;
        try {
            endDate = txtEndMonth.getText()
                    + "/" + txtEndDay.getText()
                    + "/" + txtEndYear.getText().substring(2, 4);
            //System.out.println("End Date: " + endDate);
        } catch (StringIndexOutOfBoundsException ex) {
            JOptionPane.showMessageDialog(this, "End date Entered Incorrectly\n    Please Try Again", "Date Error", JOptionPane.ERROR_MESSAGE);
        }
        return endDate;
    }

    private void cboQueriesActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cboQueriesActionPerformed
        // Leave Empty
    }//GEN-LAST:event_cboQueriesActionPerformed

    //Global arrays for JTable
    ArrayList<Integer> visualCount = new ArrayList<Integer>();
    ArrayList<String> countryList = new ArrayList<String>();
    ArrayList<String> cityList = new ArrayList<String>();
    ArrayList<String> usernameList = new ArrayList<String>();
    ArrayList<String> visualNameList = new ArrayList<String>();
    ArrayList<String> timeList = new ArrayList<String>();
    ArrayList<String> tempTimeList = new ArrayList<String>();
    ArrayList<Integer> tempvisualCount = new ArrayList<Integer>();
    ArrayList<String> tempcountryList = new ArrayList<String>();
    ArrayList<String> tempcityList = new ArrayList<String>();
    ArrayList<String> tempusernameList = new ArrayList<String>();
    ArrayList<String> tempvisualNameList = new ArrayList<String>();

    public void clearLists(){
        visualCount.clear();
        countryList.clear();
        cityList.clear();
        usernameList.clear();
        visualNameList.clear();
        tempTimeList.clear();
        tempvisualNameList.clear();
        tempvisualCount.clear();
        tempcountryList.clear();
        tempcityList.clear();
        tempusernameList.clear();
    }

    //used to claer the JTable
    int arraySize;

    public void clearTable(){
        for(int i = 0; i < arraySize + 1; i++){
            for(int j = 0; j < 4; j++){
                jtable.setValueAt("", i, j);
            }
        }
    }

    public boolean compareto(Date selectedDate, Date startDate, Date endDate){
        int start = selectedDate.compareTo(startDate);
        int end = selectedDate.compareTo(endDate);
        boolean withInBounds = true;

        if(start == -1){
            //System.out.println("before Start Date");
            withInBounds = false;
        }

        if(end == 1){
            //System.out.println("After End Date");
            withInBounds = false;
        }
        return withInBounds;

    }

    private void btnQueryActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnQueryActionPerformed
        ResultSet rs;
        DBQueries dbq = new DBQueries();

        clearTable();
        
        if (rbtnStartEndDates.isSelected()) {
            String strStartDate = getStartDate();
            String strEndDate = getEndDate();

            Date dateStart = setStringToDate(strStartDate);
            Date dateEnd = setStringToDate(strEndDate);

            // 0 = "Q1: Group by country, then city, then visualization name"
            if (cboQueries.getSelectedIndex() == 0) {
                rs = dbq.getCountryCityName();
                rsToSave = dbq.getCountryCityName();

                clearTable();

                boolean withinBounds = false;
                try {

                    while (rs.next()) {
                        
                        //System.out.println("Count: " + index);
                        tempvisualCount.add(rs.getInt(1));
                        tempcountryList.add(rs.getString("country"));
                        tempcityList.add(rs.getString("city"));
                        tempvisualNameList.add(rs.getString("visualName"));
                        tempTimeList.add(rs.getString("timeStamp"));
                        
                    }

                    //rs = dbq.getCountryCityName();

                    for(int i = 0; i < tempTimeList.size(); i++){
                        withinBounds = compareto(setStringToDate(tempTimeList.get(i)), dateStart, dateEnd);
                         System.out.println("With-in Bounds = " + withinBounds);

                        if(withinBounds){

                            
                            visualCount.add(tempvisualCount.get(i));
                            countryList.add(tempcountryList.get(i));
                            cityList.add(tempcityList.get(i));
                            visualNameList.add(tempvisualNameList.get(i));
                            timeList.add(tempTimeList.get(i));
                            
                        }
                    }

                    
                    for(int i = 0; i<visualCount.size(); i++){
                        System.out.println(visualCount.get(i));
                            System.out.print(countryList.get(i) + "\t");
                            System.out.print(cityList.get(i) + "\t");
                            System.out.print(visualNameList.get(i) + "\t");
                            System.out.println(timeList.get(i));
                    }

                    arraySize = visualCount.size();

                    jtable.setValueAt("View Count", 0, 0);
                    jtable.setValueAt("Country", 0, 1);
                    jtable.setValueAt("City", 0, 2);
                    jtable.setValueAt("Visual Name", 0, 3);


                    for (int i = 0; i < visualCount.size(); i++) {

                        System.out.println("Array Size " + visualCount.size() );
                            jtable.setValueAt(visualCount.get(i), i + 1, 0);//move down 1 row to all for title
                            jtable.setValueAt(countryList.get(i), i + 1, 1);
                            jtable.setValueAt(cityList.get(i), i + 1, 2);
                            jtable.setValueAt(visualNameList.get(i), i + 1, 3);

                            System.out.println(visualCount.get(i) + "\t" + countryList.get(i) + "\t"
                                    + cityList.get(i) + "\t"
                                    + visualNameList.get(i) + "\t" + timeList.get(i));
                        
                        
                    }

                    clearLists();
                    rs.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Error " + ex);
                    ex.printStackTrace();
                }


            }// 1 = "Q2: Group by visualization name, then country, then city"
            else if (cboQueries.getSelectedIndex() == 1) {
                rs = dbq.getNameCountryCity();
                rsToSave = dbq.getNameCountryCity();
                clearTable();

                try {
                    //rs.next();
                    int index = 0;
                    while (rs.next()) {
                        visualCount.add(rs.getInt(1));
                        visualNameList.add(rs.getString("visualName"));
                        countryList.add(rs.getString("country"));
                        cityList.add(rs.getString("city"));
                        timeList.add(rs.getString("timeStamp"));
                        index++;
                    }

                    arraySize = visualCount.size();

                    jtable.setValueAt("View Count", 0, 0);
                    jtable.setValueAt("Visual Name", 0, 1);
                    jtable.setValueAt("Country", 0, 2);
                    jtable.setValueAt("City", 0, 3);

                    for (int i = 0; i < countryList.size(); i++) {
                        Date time = setStringToDate(timeList.get(i));
                        if(dateStart.compareTo(time)>= 0 && time.compareTo(dateEnd) < 1){
                            
                            jtable.setValueAt(visualCount.get(i), i + 1, 0);
                            jtable.setValueAt(visualNameList.get(i), i + 1, 1);
                            jtable.setValueAt(countryList.get(i), i + 1, 2);
                            jtable.setValueAt(cityList.get(i), i + 1, 3);

                            System.out.println(visualCount.get(i) + "\t" + visualNameList.get(i) + "\t"
                                    + countryList.get(i) + "\t" + cityList.get(i) + "\t" + timeList.get(i));
                        }
                    }
                    clearLists();
                    rs.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Error " + ex);
                    ex.printStackTrace();
                }

            }// 2 = "Q3: Group by country, then city, then username"
            else if (cboQueries.getSelectedIndex() == 2) {
                rs = dbq.getCountryCityUsername();
                rsToSave = dbq.getCountryCityUsername();

                clearTable();

                try {
                    //rs.next();
                    int index = 0;
                    while (rs.next()) {
                        visualCount.add(rs.getInt(1));
                        countryList.add(rs.getString("country"));
                        cityList.add(rs.getString("city"));
                        usernameList.add(rs.getString(4));
                        timeList.add(rs.getString("timeStamp"));
                        index++;
                    }

                    arraySize = visualCount.size();

                    jtable.setValueAt("View Count", 0, 0);
                    jtable.setValueAt("Country", 0, 1);
                    jtable.setValueAt("City", 0, 2);
                    jtable.setValueAt("User Name", 0, 3);

                    for (int i = 0; i < countryList.size(); i++) {
                        Date time = setStringToDate(timeList.get(i));

                        if (dateStart.compareTo(time) >= 0 && time.compareTo(dateEnd) < 1) {

                            jtable.setValueAt(visualCount.get(i), i + 1, 0);
                            jtable.setValueAt(countryList.get(i), i + 1, 1);
                            jtable.setValueAt(cityList.get(i), i + 1, 2);
                            jtable.setValueAt(usernameList.get(i), i + 1, 3);

                            System.out.println(visualCount.get(i) + "\t" + countryList.get(i) + "\t"
                                    + cityList.get(i) + "\t" + usernameList.get(i) + "\t" + timeList.get(i));
                        }
                    }

                    clearLists();
                    rs.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Error " + ex);
                    ex.printStackTrace();
                }
            }
        }

        // 0 = "Q1: Group by country, then city, then visualization name"
        if (cboQueries.getSelectedIndex() == 0) {
            rs = dbq.getCountryCityName();
            rsToSave = dbq.getCountryCityName();
            clearTable();

            try {
                //rs.next();
                int index = 0;
                while (rs.next()) {
                    visualCount.add(rs.getInt(1));
                    countryList.add(rs.getString("country"));
                    cityList.add(rs.getString("city"));
                    visualNameList.add(rs.getString("visualName"));
                    timeList.add(rs.getString("timeStamp"));
                    index++;
                }

                arraySize = visualCount.size();

                jtable.setValueAt("View Count", 0, 0);
                jtable.setValueAt("Country", 0, 1);
                jtable.setValueAt("City", 0, 2);
                jtable.setValueAt("Visual Name", 0, 3);

                for (int i = 0; i < countryList.size(); i++) {
                    jtable.setValueAt(visualCount.get(i), i+1, 0);//move down 1 row to all for title
                    jtable.setValueAt(countryList.get(i), i+1, 1);
                    jtable.setValueAt(cityList.get(i), i+1, 2);
                    jtable.setValueAt(visualNameList.get(i), i+1, 3);

                    System.out.println(visualCount.get(i) + "\t" + countryList.get(i) + "\t"
                            + cityList.get(i) + "\t"
                            + visualNameList.get(i) + "\t" + timeList.get(i));
                }
                clearLists();
                rs.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error " + ex);
                ex.printStackTrace();
            }


        } // 1 = "Q2: Group by visualization name, then country, then city"
        else if (cboQueries.getSelectedIndex() == 1) {
            rs = dbq.getNameCountryCity();
            rsToSave = dbq.getNameCountryCity();
            clearTable();

            try {
                //rs.next();
                int index = 0;
                while (rs.next()) {
                    visualCount.add(rs.getInt(1));
                    visualNameList.add(rs.getString("visualName"));
                    countryList.add(rs.getString("country"));
                    cityList.add(rs.getString("city"));
                    timeList.add(rs.getString("timeStamp"));
                    index++;
                }

                arraySize = visualCount.size();

                jtable.setValueAt("View Count", 0, 0);
                jtable.setValueAt("Visual Name", 0, 1);
                jtable.setValueAt("Country", 0, 2);
                jtable.setValueAt("City", 0, 3);

                for (int i = 0; i < countryList.size(); i++) {
                    jtable.setValueAt(visualCount.get(i), i+1, 0);
                    jtable.setValueAt(visualNameList.get(i), i+1, 1);
                    jtable.setValueAt(countryList.get(i), i+1, 2);
                    jtable.setValueAt(cityList.get(i), i+1, 3);

                    System.out.println(visualCount.get(i) + "\t" + visualNameList.get(i) + "\t"
                            + countryList.get(i) + "\t" + cityList.get(i) + "\t" + timeList.get(i));
                }
                clearLists();
                rs.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error " + ex);
                ex.printStackTrace();
            }

        } // 2 = "Q3: Group by country, then city, then username"
        else if (cboQueries.getSelectedIndex() == 2) {
            rs = dbq.getCountryCityUsername();
            rsToSave = dbq.getCountryCityUsername();

            clearTable();
            try {
                //rs.next();
                int index = 0;
                while (rs.next()) {
                    visualCount.add(rs.getInt(1));
                    countryList.add(rs.getString("country"));
                    cityList.add(rs.getString("city"));
                    usernameList.add(rs.getString(4));
                    timeList.add(rs.getString("timeStamp"));
                    index++;
                }

                arraySize = visualCount.size();

                jtable.setValueAt("View Count", 0, 0);
                jtable.setValueAt("Country", 0, 1);
                jtable.setValueAt("City", 0, 2);
                jtable.setValueAt("User Name", 0, 3);

                for (int i = 0; i < countryList.size(); i++) {
                    jtable.setValueAt(visualCount.get(i), i+1, 0);
                    jtable.setValueAt(countryList.get(i), i+1, 1);
                    jtable.setValueAt(cityList.get(i), i+1, 2);
                    jtable.setValueAt(usernameList.get(i), i+1, 3);

                    System.out.println(visualCount.get(i) + "\t" + countryList.get(i) + "\t"
                            + cityList.get(i) + "\t" + usernameList.get(i) + "\t" + timeList.get(i));
                }
                clearLists();
                rs.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error " + ex);
                ex.printStackTrace();
            }

        }

        //if everything goes well let users now save as
        menuSaveAs.setEnabled(true);
    }//GEN-LAST:event_btnQueryActionPerformed

    private void txtEndYearActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtEndYearActionPerformed

}//GEN-LAST:event_txtEndYearActionPerformed

    private void txtEndDayActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtEndDayActionPerformed

}//GEN-LAST:event_txtEndDayActionPerformed

    private void txtEndMonthActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtEndMonthActionPerformed

}//GEN-LAST:event_txtEndMonthActionPerformed

    private void txtStartMonthActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtStartMonthActionPerformed

    }//GEN-LAST:event_txtStartMonthActionPerformed

    private void txtStartDayActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtStartDayActionPerformed

    }//GEN-LAST:event_txtStartDayActionPerformed

    private void txtStartYearActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtStartYearActionPerformed

    }//GEN-LAST:event_txtStartYearActionPerformed

    private void rbtnStartEndDatesActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_rbtnStartEndDatesActionPerformed
        lblStartDate.setEnabled(true);
        txtStartMonth.setEnabled(true);
        lblDash1.setEnabled(true);
        txtStartDay.setEnabled(true);
        lblDash2.setEnabled(true);
        txtStartYear.setEnabled(true);

        lblEndDate.setEnabled(true);
        txtEndMonth.setEnabled(true);
        lblDash3.setEnabled(true);
        txtEndDay.setEnabled(true);
        lblDash4.setEnabled(true);
        txtEndYear.setEnabled(true);
    }//GEN-LAST:event_rbtnStartEndDatesActionPerformed

    private void rbtnAllDatesActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_rbtnAllDatesActionPerformed
        lblStartDate.setEnabled(false);
        txtStartMonth.setEnabled(false);
        lblDash1.setEnabled(false);
        txtStartDay.setEnabled(false);
        lblDash2.setEnabled(false);
        txtStartYear.setEnabled(false);

        lblEndDate.setEnabled(false);
        txtEndMonth.setEnabled(false);
        lblDash3.setEnabled(false);
        txtEndDay.setEnabled(false);
        lblDash4.setEnabled(false);
        txtEndYear.setEnabled(false);

    }//GEN-LAST:event_rbtnAllDatesActionPerformed

    private void menuExitActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_menuExitActionPerformed
        System.exit(0);
    }//GEN-LAST:event_menuExitActionPerformed

    private void menuSaveAsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_menuSaveAsActionPerformed
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setAcceptAllFileFilterUsed(false);
        FileFilter csvFileFilter = new FileNameExtensionFilter("Comma-seperated value files (*.csv)", ".csv");
        fileChooser.addChoosableFileFilter(csvFileFilter);

        int saveResult = fileChooser.showSaveDialog(this);
        if (saveResult != JFileChooser.APPROVE_OPTION) {
            return;
        }

        File file = fileChooser.getSelectedFile();
        String filename = file.getAbsolutePath() + ".csv";

        try {
            CSVWriter writer = new CSVWriter(new FileWriter(filename));
            writer.writeAll(rsToSave, includeHeadersInCSVFile);
            writer.flush();
            writer.close();
        } catch (Exception e) {
            //IOException & SQLException
            System.out.println("Error when saving file: " + e);
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Sorry, could not save data to file", "Unsucessful Save", JOptionPane.ERROR_MESSAGE);
        }

    }//GEN-LAST:event_menuSaveAsActionPerformed

    private void jMenuItem1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jMenuItem1ActionPerformed
        //open user manual
        File file = new File("UserManual.pdf");
        try {
            Desktop.getDesktop().open(file);
	} catch (IOException e) { }
    }//GEN-LAST:event_jMenuItem1ActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new gui().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnQuery;
    private javax.swing.ButtonGroup btngDates;
    private javax.swing.JComboBox cboQueries;
    private javax.swing.JMenu jMenu1;
    private javax.swing.JMenuBar jMenuBar1;
    private javax.swing.JMenuItem jMenuItem1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JPopupMenu.Separator jSeparator1;
    private javax.swing.JTable jtable;
    private javax.swing.JLabel lblDash1;
    private javax.swing.JLabel lblDash2;
    private javax.swing.JLabel lblDash3;
    private javax.swing.JLabel lblDash4;
    private javax.swing.JLabel lblEndDate;
    private javax.swing.JLabel lblStartDate;
    private javax.swing.JMenuItem menuExit;
    private javax.swing.JMenu menuManual;
    private javax.swing.JMenuItem menuSaveAs;
    private javax.swing.JRadioButton rbtnAllDates;
    private javax.swing.JRadioButton rbtnStartEndDates;
    private javax.swing.JTextField txtEndDay;
    private javax.swing.JTextField txtEndMonth;
    private javax.swing.JTextField txtEndYear;
    private javax.swing.JTextField txtStartDay;
    private javax.swing.JTextField txtStartMonth;
    private javax.swing.JTextField txtStartYear;
    // End of variables declaration//GEN-END:variables
}
